﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;


using com.crawler.db.Interface;
using com.crawler.common;
using System.Reflection;
using System.IO;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;

namespace com.crawler.db.MySQL
{
    public class DataAccess : DataManager
    {

        static object m_single_obj = new object();

        /// <summary>
        /// 获取小说信息
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public BookInfo GetBookInfoById(long i_book_id, string i_connect_string)
        {
            string t_str_sql = string.Format("select bookName,AuthorName,categoryName,subCategoryName,Description,siteId,siteName,lastUpdateChapterId,lastUpdateChapterName,lastUpdateTime,chapterListUrl,chapterCount from tbbook where bookId={0}", i_book_id);

            BookInfo t_book = null;

            try
            {
                DataSet t_ds = DBHelper.Query(t_str_sql, i_connect_string);
                if (t_ds != null && t_ds.Tables.Count > 0 && t_ds.Tables[0].Rows.Count > 0)
                {
                    t_book = new BookInfo();
                    t_book.BookId = i_book_id;
                    t_book.ChapterListUrl = t_ds.Tables[0].Rows[0]["chapterListUrl"].ToString();
                    t_book.BookName = t_ds.Tables[0].Rows[0]["bookName"].ToString();
                    t_book.AuthorName = t_ds.Tables[0].Rows[0]["AuthorName"].ToString();
                    t_book.CategoryName = t_ds.Tables[0].Rows[0]["categoryName"].ToString();
                    t_book.SubCategoryName = t_ds.Tables[0].Rows[0]["subCategoryName"].ToString();
                    t_book.Description = t_ds.Tables[0].Rows[0]["Description"].ToString();
                    t_book.SiteId = Convert.ToInt32(t_ds.Tables[0].Rows[0]["SiteId"].ToString());
                    t_book.SiteName = t_ds.Tables[0].Rows[0]["siteName"].ToString();
                    t_book.LastUpdateChapterId = t_ds.Tables[0].Rows[0]["lastUpdateChapterId"] == DBNull.Value ? 0 : Convert.ToInt32(t_ds.Tables[0].Rows[0]["lastUpdateChapterId"].ToString());
                    t_book.LastUpdateChapterName = t_ds.Tables[0].Rows[0]["lastUpdateChapterName"] == DBNull.Value ? "" : t_ds.Tables[0].Rows[0]["lastUpdateChapterName"].ToString();
                    t_book.LastUpdateChapterTime = t_ds.Tables[0].Rows[0]["lastUpdateTime"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(t_ds.Tables[0].Rows[0]["lastUpdateTime"].ToString());
                    t_book.ChapterCount = t_ds.Tables[0].Rows[0]["chapterCount"] == DBNull.Value ? 0 : Convert.ToInt32(t_ds.Tables[0].Rows[0]["chapterCount"].ToString());
                }

                return t_book;
            }
            catch (Exception ex)
            {
                throw new DataException("获取小说信息失败", ex);
            }
        }

        /// <summary>
        /// 插入站点信息
        /// </summary>
        /// <param name="i_site_id"></param>
        /// <param name="i_site_name"></param>
        /// <param name="i_tb_name"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public int InsertBookSite(int i_site_id, string i_site_name, string i_tb_name, string i_site_enconding, string i_chapter_content_reg, string i_chapter_content_filter, string i_connect_string)
        {

            try
            {

                StringBuilder t_sb = new StringBuilder();
                t_sb.Append("insert into tbBookSite(siteId,siteName,tbName,siteEnconding,chapterContentReg,chapterContentFilter)values");


                t_sb.AppendFormat("({0},'{1}','{2}','{3}','{4}','{5}')", i_site_id, i_site_name, i_tb_name, i_site_enconding, i_chapter_content_reg, i_chapter_content_filter);


                return DBHelper.ExecuteSql(t_sb.ToString(), i_connect_string);

            }
            catch (Exception ex)
            {
                throw new DataException("插入站点信息失败", ex);
            }
        }

        /// <summary>
        /// 初始化站点到数据库
        /// </summary>
        /// <param name="i_book_site_list"></param>
        /// <returns></returns>
        public int InitBookSite(List<BookSiteInfo> i_book_site_list, string i_connect_string)
        {
            string t_str_sql = "delete from tbbookSite";

            try
            {
                DBHelper.ExecuteSql(t_str_sql, i_connect_string);


                StringBuilder t_sb = new StringBuilder();
                t_sb.Append("insert into tbBookSite(siteId,siteName,tbName,siteEnconding,chapterContentReg,chapterContentFilter)values");

                foreach (BookSiteInfo t_site in i_book_site_list)
                {
                    t_sb.AppendFormat("({0},'{1}','{2}','{3}','{4}','{5}'),", t_site.SiteId, t_site.SiteName, t_site.TbName, t_site.SiteEncoding, t_site.ChapterContentReg, t_site.ChapterContentFilterReg);
                }

                return DBHelper.ExecuteSql(t_sb.ToString().TrimEnd(','), i_connect_string);

            }
            catch (Exception ex)
            {
                throw new DataException("初始化站点信息失败", ex);
            }
        }



        /// <summary>
        /// 获取站点图书信息
        /// </summary>
        /// <param name="i_book_name"></param>
        /// <param name="i_site_id"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public BookInfo GetBookInfoByName(string i_book_name, string i_connect_string)
        {
            BookInfo t_book = null;

            try
            {
                lock (m_single_obj)
                {

                    string t_sql = string.Format("select bookId,ChapterListUrl,lastUpdateChapterName from tbbook where bookName='{0}' ", i_book_name);
                    DataSet t_ds = DBHelper.Query(t_sql, i_connect_string);
                    if (t_ds != null && t_ds.Tables.Count > 0 && t_ds.Tables[0].Rows.Count > 0)
                    {
                        t_book = new BookInfo();
                        t_book.BookId = Convert.ToInt64(t_ds.Tables[0].Rows[0]["bookId"].ToString());
                        t_book.ChapterListUrl = t_ds.Tables[0].Rows[0]["ChapterListUrl"].ToString();
                        t_book.BookName = i_book_name;
                        t_book.LastUpdateChapterName = t_ds.Tables[0].Rows[0]["LastUpdateChapterName"].ToString();


                    }
                    return t_book;
                }
            }
            catch (Exception ex)
            {
                throw new DataException("获取图书信息失败", ex);
            }
        }

        /// <summary>
        /// 添加小说信息
        /// </summary>
        /// <param name="i_book"></param>
        /// <returns></returns>
        public long AddBook(BookInfo i_book, string i_connect_string)
        {
             lock (m_single_obj)
             {
                try
                {
                    System.Threading.Thread.Sleep(500);
                    long t_book_id = 0;

                    //string t_str_sql = string.Format("select bookName from tbCrawlerBook where bookName='{0}' and siteId={1}", i_book.BookName.Replace("'",""), i_book.SiteId);

                    //using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                    //{
                    //    using (MySqlCommand cmd = new MySqlCommand(t_str_sql, t_con))
                    //    {
                    //        cmd.CommandTimeout = 600;
                    //        t_con.Open();
                    //        using (MySqlDataReader t_reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    //        {
                    //            while (t_reader.Read())
                    //            {
                    //                if (!string.IsNullOrEmpty(t_reader["bookName"].ToString()))
                    //                {
                    //                    t_book_id = -1;
                    //                    break;
                    //                }
                    //            }
                    //        }
                    //    }
                    //    if (t_con.State == ConnectionState.Open)
                    //    {
                    //        t_con.Close();
                    //    }
                    //}

                    //if (t_book_id == -1)
                    //    return t_book_id;

                    ////先把地址添加到搜索列表中
                    // t_str_sql=string.Format( "insert into tbCrawlerBook(bookName,chapterListUrl,siteId)values('{0}','{1}',{2})",i_book.BookName.Replace("'",""),i_book.ChapterListUrl.Replace("'",""),i_book.SiteId);
                    // int t_ret = -1;
                    // using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                    // {
                    //     using (MySqlCommand cmd = new MySqlCommand(t_str_sql, t_con))
                    //     {
                             
                    //         cmd.CommandTimeout = 600;
                    //         t_con.Open();
                    //         t_ret = cmd.ExecuteNonQuery();
                                
                    //     }

                    //     if (t_con.State == ConnectionState.Open)
                    //     {
                    //         t_con.Close();
                    //     }
                    // }

                    //if (t_ret < 0)
                    //    return -1;

                   string  t_str_sql = string.Format("select bookId,bookName from tbbook where bookName='{0}'", i_book.BookName.Replace("'",""));
                    using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                    {
                        using (MySqlCommand cmd = new MySqlCommand(t_str_sql, t_con))
                        {
                            cmd.CommandTimeout = 600;
                            t_con.Open();
                            using (MySqlDataReader t_reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                while (t_reader.Read())
                                {
                                    if (!long.TryParse(t_reader["bookId"].ToString(), out t_book_id))
                                        t_book_id = -1;
                                }
                            }
                        }
                        if (t_con.State == ConnectionState.Open)
                        {
                            t_con.Close();
                        }
                    }

                    if (t_book_id > 0)
                        return t_book_id;

                    int t_ret = -1;
                    t_str_sql = "insert into tbbook(bookName,AuthorName,categoryName,subCategoryName,ICONUrl,ChapterListUrl,Description,siteId,siteName)" +
                   string.Format("values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}')", i_book.BookName.Replace("'",""), i_book.AuthorName.Replace("'",""), i_book.CategoryName.Replace("'",""),
                   i_book.SubCategoryName.Replace("'",""), i_book.ICONUrl.Replace("'",""), i_book.ChapterListUrl.Replace("'",""), i_book.Description.Replace("'",""), i_book.SiteId, i_book.SiteName);

                   using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                   {
                       using (MySqlCommand cmd = new MySqlCommand(t_str_sql, t_con))
                       {

                           cmd.CommandTimeout = 600;
                           t_con.Open();
                           t_ret = cmd.ExecuteNonQuery();

                       }
                       if (t_con.State == ConnectionState.Open)
                       {
                           t_con.Close();
                       }
                   }
                    
                    if (t_ret > 0)
                    {
                        t_str_sql = string.Format("select bookId from tbbook where  bookName='{0}'", i_book.BookName.Replace("'", ""), i_book.SiteId);
                        using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                        {
                            using (MySqlCommand cmd = new MySqlCommand(t_str_sql, t_con))
                            {
                                cmd.CommandTimeout = 600;
                                t_con.Open();
                                using (MySqlDataReader t_reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                                {
                                    while (t_reader.Read())
                                    {
                                        if (!long.TryParse(t_reader["bookId"].ToString(), out t_book_id))
                                            t_book_id = -1;
                                    }
                                }
                            }

                            if (t_con.State == ConnectionState.Open)
                            {
                                t_con.Close();
                            }
                        }
                        

                    }
                    return t_book_id;
                   
                }
                catch (Exception ex)
                {
                    LogHelper.Info("添加小说信息失败" + ex.ToString() + "bookInfo=" + i_book.ToString()+"connstring=" + i_connect_string);
                    throw new DataException("添加小说信息失败", ex);
                }

             }

        }

        /// <summary>
        /// 批量插入章节信息
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <param name="i_book_name"></param>
        /// <param name="i_chapter_list"></param>
        /// <param name="i_connect_string"></param>
        /// <param name="i_table_name"></param>
        /// <returns></returns>
        public int AddChapterList(long i_book_id, string i_book_name, List<ChapterInfo> i_chapter_list, string i_connect_string, string i_table_name, int i_site_id,string i_site_name)
        {
            lock (m_single_obj)
            {
                string t_sql = string.Format("select chapterId,chapterIndex from {0} where bookId={1} order by chapterId desc limit 1", i_table_name, i_book_id);
                try
                {
              
                    if (i_chapter_list == null || i_chapter_list.Count == 0)
                        return 0;

                    int t_new_chapter_count = 0;

                    int t_chapter_index = 0;
                    long t_chapter_id = 0;
                    long t_pre_chapter_id = 0;
                    long t_next_chapter_id = 0;
                    long t_last_chapter_id = 0;//上次更新的最后一个章节ID
                    long t_now_first_chapter_id = 0;//本次更新的最新一个ID

                    using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                    {
                        using (MySqlCommand cmd = new MySqlCommand(t_sql, t_con))
                        {
                            cmd.CommandTimeout = 600;
                            t_con.Open();
                            using (MySqlDataReader t_reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                while (t_reader.Read())
                                {

                                    if (!long.TryParse(t_reader["chapterId"].ToString(), out t_pre_chapter_id))
                                        t_pre_chapter_id = 0;
                                    t_last_chapter_id = t_pre_chapter_id;
                                    if (!int.TryParse(t_reader["chapterIndex"].ToString(), out t_chapter_index))
                                        t_chapter_index = 0;
                                }
                            }
                        }
                        if (t_con.State == ConnectionState.Open)
                        {
                            t_con.Close();
                        }
                    }


                    if (t_chapter_index == i_chapter_list.Count - 1)
                        return 0;


                    string t_chapter_name = string.Empty;
                    for (int index = 0; index < i_chapter_list.Count; index++)
                    {
                        t_chapter_name = i_chapter_list[index].ChapterName;
                        t_chapter_name = Regex.Replace(t_chapter_name, "\\s", "", RegexOptions.IgnoreCase | RegexOptions.Compiled);
                        if (t_chapter_name.IndexOf("第一章") > -1 || t_chapter_name.IndexOf("第1章") > -1
                            || t_chapter_name.IndexOf("第壹章") > -1)
                        {

                            t_new_chapter_count = i_chapter_list.Count - index;
                            break;
                        }
                    }



                    if (t_chapter_index != 0)
                    {
                        t_chapter_index = t_chapter_index + 1;
                    }

                    t_sql = string.Format("select chapterId from {0} order by chapterId desc limit 1", i_table_name);

                    using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                    {
                        using (MySqlCommand cmd = new MySqlCommand(t_sql, t_con))
                        {
                            cmd.CommandTimeout = 600;
                            t_con.Open();
                            using (MySqlDataReader t_reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                while (t_reader.Read())
                                {

                                    if (!long.TryParse(t_reader["chapterId"].ToString(), out t_chapter_id))
                                        t_chapter_id = 0;

                                }
                            }
                        }
                        if (t_con.State == ConnectionState.Open)
                        {
                            t_con.Close();
                        }
                    }

                    t_chapter_id++;
                    t_now_first_chapter_id = t_chapter_id;

                    StringBuilder t_sb = new StringBuilder();
                    t_sb.Append(string.Format("insert into {0}(bookId,ChapterName,ChapterUrl,BookName,UpdateTime,PreChapterId,NextChapterId,ChapterId,chapterIndex)values", i_table_name));

                    t_next_chapter_id = t_chapter_id + 1;

                    for (int index = t_chapter_index; index < i_chapter_list.Count; index++)
                    {
                        t_sb.AppendFormat("({0},'{1}','{2}','{3}','{4}',{5},{6},{7},{8}),", i_book_id, i_chapter_list[index].ChapterName.Replace("'", ""), i_chapter_list[index].ChapterUrl.Replace("'", ""),
                            i_book_name.Replace("'", ""), i_chapter_list[index].UpdateTime, t_pre_chapter_id, (index == i_chapter_list.Count - 1) ? 0 : t_next_chapter_id, t_chapter_id, index);

                        t_pre_chapter_id = t_chapter_id;
                        t_chapter_id = t_next_chapter_id;
                        t_next_chapter_id++;
                    }

                    int t_ret = -1;

                    using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                    {
                        using (MySqlCommand cmd = new MySqlCommand(t_sb.ToString().TrimEnd(','), t_con))
                        {

                            cmd.CommandTimeout = 600;
                            t_con.Open();
                            t_ret = cmd.ExecuteNonQuery();

                        }

                        if (t_con.State == ConnectionState.Open)
                        {
                            t_con.Close();
                        }
                    }

                    t_sql = string.Format("select chapterCount from tbbook where bookId={0} ", i_book_id);

                    int t_chapter_count = 0;

                    using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                    {
                        using (MySqlCommand cmd = new MySqlCommand(t_sql, t_con))
                        {
                            cmd.CommandTimeout = 600;
                            t_con.Open();
                            using (MySqlDataReader t_reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                while (t_reader.Read())
                                {

                                    if (!int.TryParse(t_reader["chapterCount"].ToString(), out t_chapter_count))
                                        t_chapter_count = 0;

                                }
                            }
                        }

                        if (t_con.State == ConnectionState.Open)
                        {
                            t_con.Close();
                        }
                    }


                    if (t_chapter_count < t_new_chapter_count)
                    {
                        if (t_ret > 0)
                        {
                            if (t_last_chapter_id > 0)
                            {
                                t_sql = string.Format("update {0} set nextChapterId={1} where chapterId={2}", i_table_name, t_now_first_chapter_id, t_last_chapter_id);

                                using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                                {
                                    using (MySqlCommand cmd = new MySqlCommand(t_sql, t_con))
                                    {

                                        cmd.CommandTimeout = 600;
                                        t_con.Open();
                                        t_ret = cmd.ExecuteNonQuery();

                                    }
                                    if (t_con.State == ConnectionState.Open)
                                    {
                                        t_con.Close();
                                    }
                                }
                            }
                            t_sql = string.Format("update tbBook set lastUpdateChapterId={0},lastUpdateChapterName='{1}',lastUpdateTime='{2}',chapterCount={3},siteId={4} where bookId={5}",
                  t_pre_chapter_id, i_chapter_list[i_chapter_list.Count - 1].ChapterName.Replace("'", ""), i_chapter_list[i_chapter_list.Count - 1].UpdateTime, t_new_chapter_count, i_site_id, i_book_id);

                            using (MySqlConnection t_con = new MySqlConnection(i_connect_string))
                            {
                                using (MySqlCommand cmd = new MySqlCommand(t_sql, t_con))
                                {

                                    cmd.CommandTimeout = 600;
                                    t_con.Open();
                                    t_ret = cmd.ExecuteNonQuery();

                                }

                                if (t_con.State == ConnectionState.Open)
                                {
                                    t_con.Close();
                                }
                            }
                            return i_chapter_list.Count - t_chapter_index;
                        }
                    }
                    return 0;
                }
                catch (Exception ex)
                {
                    LogHelper.Info("添加小说章节失败,bookId=" + i_book_id.ToString() + ",bookName=" + i_book_name + ",constring=" + i_connect_string + ",str_sql=" + t_sql);
                    throw new DataException("批量插入小说章节失败", ex);
                }
            }
           
        }

        /// <summary>
        /// 添加章节信息
        /// </summary>
        /// <param name="i_chapter"></param>
        /// <returns></returns>
        public long AddChapter(ChapterInfo i_chapter, string i_connect_string, string i_table_name)
        {

            try
            {
                lock (m_single_obj)
                {
                    string t_sql = string.Format("select chapterId from {0} where bookId={1} order by UpdateTime desc limit 1", i_table_name, i_chapter.BookId);

                    DataSet t_ds = DBHelper.Query(t_sql, i_connect_string);
                    long t_pre_chapter_id = 0;
                    long t_next_chapter_id = 0;
                    if (t_ds != null && t_ds.Tables.Count > 0 && t_ds.Tables[0].Rows.Count > 0)
                    {
                        if (!long.TryParse(t_ds.Tables[0].Rows[0]["chapterId"].ToString(), out t_pre_chapter_id))
                            t_pre_chapter_id = 0;
                    }
                    i_chapter.PreChapterId = t_pre_chapter_id;
                    t_sql = string.Format("insert into {0}(bookId,ChapterName,ChapterUrl,BookName,UpdateTime,PreChapterId,NextChapterId)", i_table_name) +
                string.Format("values({0},'{1}','{2}','{3}','{4}',{5},{6})", i_chapter.BookId, i_chapter.ChapterName, i_chapter.ChapterUrl,
                i_chapter.BookName, i_chapter.UpdateTime, i_chapter.PreChapterId, i_chapter.NextChapterId);

                    int t_ret = DBHelper.ExecuteSql(t_sql, i_connect_string);
                    if (t_ret > 0)
                    {
                        t_sql = string.Format("select chapterId from {0} where bookId={1} order by UpdateTime desc limit 1", i_table_name, i_chapter.BookId);
                        t_ds = DBHelper.Query(t_sql, i_connect_string);
                        if (t_ds != null && t_ds.Tables.Count > 0 && t_ds.Tables[0].Rows.Count > 0)
                        {
                            if (!long.TryParse(t_ds.Tables[0].Rows[0]["chapterId"].ToString(), out t_next_chapter_id))
                                t_next_chapter_id = 0;
                        }
                        t_sql = string.Format("update {0} set NextChapterId={1} where chapterId={2}", i_table_name, t_next_chapter_id, t_pre_chapter_id);
                        DBHelper.ExecuteSql(t_sql, i_connect_string);
                    }
                    return t_ret;
                }
            }
            catch (Exception ex)
            {
                throw new DataException("添加小说信息失败", ex);
            }
        }

        /// <summary>
        /// 获取某本小说的最后更新章节ID
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <returns></returns>
        public long GetLastUpdateChapterId(long i_book_id, string i_connect_string, string i_table_name)
        {
            string t_sql = string.Format("select max(chapterId) chapterId from {0} where bookId={1}", i_table_name, i_book_id);

            long t_last_update_chapter_id = 0;
            try
            {
                DataSet ds = DBHelper.Query(t_sql, i_connect_string);

                if (ds != null && ds.Tables.Count > 0)
                {
                    if (!long.TryParse(ds.Tables[0].Rows[0]["chapterId"].ToString(), out t_last_update_chapter_id))
                        t_last_update_chapter_id = 0;
                }
                return t_last_update_chapter_id;
            }
            catch (Exception ex)
            {
                throw new DataException("获取小说：" + i_book_id.ToString() + "的最新更新章节ID失败", ex);
            }
        }

        /// <summary>
        /// 更新书籍最近跟新时间
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <param name="i_last_update_chapter_id"></param>
        /// <param name="i_last_update_chapter_name"></param>
        /// <param name="i_last_update_time"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public int UpdateBook(long i_book_id, string i_connect_string, string i_table_name, int i_chapter_count)
        {
            string t_sql = string.Format("select chapterId,chapterName,UpdateTime from {0} where bookId={1} order by UpdateTime desc limit 1", i_table_name, i_book_id);
            try
            {
                DataSet t_ds = DBHelper.Query(t_sql, i_connect_string);
                if (t_ds != null && t_ds.Tables.Count > 0 && t_ds.Tables[0].Rows.Count > 0)
                {
                    t_sql = string.Format("update tbBook set lastUpdateChapterId={0},lastUpdateChapterName='{1}',lastUpdateTime='{2}',chapterCount={3} where bookId={4}",
               t_ds.Tables[0].Rows[0]["chapterId"], t_ds.Tables[0].Rows[0]["chapterName"], t_ds.Tables[0].Rows[0]["updateTime"], i_chapter_count, i_book_id);
                    return DBHelper.ExecuteSql(t_sql, i_connect_string);
                }
                else
                {
                    return -1;
                }

            }
            catch (Exception ex)
            {
                throw new DataException("更新小说最新更新章节信息失败", ex);
            }
        }

        /// <summary>
        /// 创建表结构
        /// </summary>
        /// <param name="i_table_name"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public int CreateTable(string i_table_name, string i_connect_string)
        {
            string t_sql = LoadSqlFromAssembly("CreateTable.txt");

            if (!string.IsNullOrEmpty(t_sql))
            {
                t_sql = string.Format(t_sql, i_table_name);

            }

            try
            {
                return DBHelper.ExecuteSql(t_sql, i_connect_string);
            }
            catch (Exception ex)
            {
                throw new DataException("创建表结构失败", ex);
            }
        }

        /// <summary>
        /// 获取需要更新章节的图书列表
        /// </summary>
        /// <returns></returns>
        public List<BookInfo> GetUpdateBookList(string i_connect_string)
        {
            string t_str_sql = "select a.bookId,a.bookName,siteId,siteName, chapterlisturl,lastUpdateChapterId,lastUpdateChapterName,ChapterCount from tbbook a,tbbookclick b where a.bookId=b.bookId ";

            t_str_sql = "select a.bookId,a.bookName,siteId,siteName, chapterlisturl,lastUpdateChapterId,lastUpdateChapterName,ChapterCount from tbbook a where a.bookId=4 ";

            List<BookInfo> t_list = null;
            try
            {
                DataSet t_ds = DBHelper.Query(t_str_sql, i_connect_string);

                if (t_ds != null && t_ds.Tables.Count > 0 && t_ds.Tables[0].Rows.Count > 0)
                {
                    BookInfo t_book = null;

                    t_list = new List<BookInfo>();

                    foreach (DataRow t_dr in t_ds.Tables[0].Rows)
                    {
                        t_book = new BookInfo();
                        t_book.ChapterListUrl = t_dr["chapterlisturl"].ToString();
                        t_book.BookId = Convert.ToInt64(t_dr["bookId"].ToString());
                        t_book.BookName = t_dr["bookName"].ToString();
                        t_book.LastUpdateChapterName = t_dr["lastUpdateChapterName"].ToString();
                        t_book.LastUpdateChapterId = t_dr["lastUpdateChapterId"] == DBNull.Value ? 0 : Convert.ToInt64(t_dr["lastUpdateChapterId"].ToString());
                        t_book.SiteId = Convert.ToInt32(t_dr["siteId"].ToString());
                        t_book.SiteName = t_dr["siteName"].ToString();
                        t_book.ChapterCount = t_dr["ChapterCount"] == DBNull.Value ? 0 : Convert.ToInt32(t_dr["ChapterCount"].ToString());
                        t_list.Add(t_book);
                    }
                }
                return t_list;
            }
            catch (Exception ex)
            {
                LogHelper.Error("获取重点更新列表失败" + ex.ToString());
                return null;
            }
        }


        private string LoadSqlFromAssembly(string i_file_name)
        {
            //得到当前程序集对象
            Assembly t_assembly = Assembly.GetExecutingAssembly();
            //创建sql脚本的流对象
            Stream t_strm = t_assembly.GetManifestResourceStream(string.Format("com.crawler.db.MySQL.{0}", i_file_name));

            //读sql流对象
            StreamReader reader = new StreamReader(t_strm, System.Text.Encoding.Default);
            return reader.ReadToEnd();
        }

        /// <summary>
        /// 获取图书信息
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <returns></returns>
        public BookInfo GetBookInfo(long i_book_id, string i_connect_string)
        {
            string t_sql = string.Format("select * from tbbook where bookId={0}", i_book_id);
            BookInfo t_book = null;
            try
            {
                DataSet t_ds = DBHelper.Query(t_sql, i_connect_string);
                if (t_ds != null && t_ds.Tables.Count > 0 && t_ds.Tables[0].Rows.Count > 0)
                {
                    t_book = new BookInfo();
                    t_book.BookId = i_book_id;
                    t_book.BookName = t_ds.Tables[0].Rows[0]["bookName"].ToString();
                    t_book.AuthorName = t_ds.Tables[0].Rows[0]["AuthorName"].ToString();
                    t_book.CategoryName = t_ds.Tables[0].Rows[0]["CategoryName"].ToString();
                    t_book.SubCategoryName = t_ds.Tables[0].Rows[0]["SubCategoryName"].ToString();
                    t_book.Description = t_ds.Tables[0].Rows[0]["Description"].ToString();
                    t_book.SiteId = Convert.ToInt32(t_ds.Tables[0].Rows[0]["SiteId"].ToString());
                    t_book.SiteName = t_ds.Tables[0].Rows[0]["SiteName"].ToString();
                    t_book.LastUpdateChapterId = t_ds.Tables[0].Rows[0]["LastUpdateChapterId"] == DBNull.Value ? 0 : Convert.ToInt64(t_ds.Tables[0].Rows[0]["LastUpdateChapterId"].ToString());
                    t_book.LastUpdateChapterName = t_ds.Tables[0].Rows[0]["LastUpdateChapterName"].ToString();
                    t_book.LastUpdateChapterTime = t_ds.Tables[0].Rows[0]["lastUpdateTime"] == DBNull.Value ? DateTime.Now.AddYears(-1) : Convert.ToDateTime(t_ds.Tables[0].Rows[0]["lastUpdateTime"].ToString());
                }
                return t_book;
            }
            catch (Exception ex)
            {
                throw new DataException("获取图书详情失败", ex);
            }
        }

        /// <summary>
        /// 初始化数据库脚本
        /// </summary>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public int InitDBScript(string i_connect_string)
        {
            string t_sql = LoadSqlFromAssembly("DB.txt");
            try
            {
                if (!string.IsNullOrEmpty(t_sql))
                {
                    string[] t_sql_arr = t_sql.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                    if (t_sql_arr != null && t_sql_arr.Length > 0)
                    {
                        foreach (string t_db_sql in t_sql_arr)
                        {
                            DBHelper.ExecuteSql(t_db_sql, i_connect_string);
                        }
                    }

                }
                return 1;
            }
            catch (Exception ex)
            {
                throw new DataException("初始化数据库脚本失败", ex);
            }

        }



        /// <summary>
        /// 获取需要更新的小说队列
        /// </summary>
        /// <param name="i_site_list"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public Queue<SiteUrl> GetUpdateBook(List<BookSiteInfo> i_site_list, int i_page_index, string i_connect_string)
        {
            string t_str_sql = string.Format("select a.bookId,a.bookName,a.siteId,a.lastUpdateChapterName, b.chapterListUrl from tbbook a,tbcrawlerbook b where a.bookName=b.bookName limit {0},10000", (i_page_index - 1) * 10000 + 1);

            //存放站点对应的小说集合，这里的50表示有50个站点，这个值可以从数据库获得
            List<SiteUrl>[] t_book_list_arr = new List<SiteUrl>[i_site_list.Count];
            //小说信息
            SiteUrl t_book = null;
            //需要返回的小说队列
            Queue<SiteUrl> t_book_queue = new Queue<SiteUrl>();
            try
            {
                using (MySqlConnection t_connection = new MySqlConnection(i_connect_string))
                {
                    using (MySqlCommand t_cmd = new MySqlCommand(t_str_sql, t_connection))
                    {
                        try
                        {
                            t_cmd.CommandTimeout = 600;
                            t_connection.Open();
                            MySqlDataReader t_reader = t_cmd.ExecuteReader(CommandBehavior.CloseConnection);
                            //把数据读取到站点对应的小说结合中
                            while (t_reader.Read())
                            {
                                if (t_reader["lastUpdateChapterName"] != DBNull.Value)
                                {
                                    if (IsComplateBook(t_reader["lastUpdateChapterName"].ToString()))
                                        continue;
                                }

                                t_book = new SiteUrl();
                                t_book.SiteId = Convert.ToInt32(t_reader["siteId"].ToString());
                                t_book.BookId = Convert.ToInt64(t_reader["bookId"].ToString());
                                t_book.Url = t_reader["chapterListUrl"].ToString();
                                t_book.BookName = t_reader["bookName"].ToString();
                                t_book.BookType = Consts.BookPageType.ChapterList;
                                t_book.ChapterOrder = GetChapterOrderType(i_site_list, t_book.SiteId);
                                //该站点已经读取到小说
                                if (t_book_list_arr[t_book.SiteId - 1] != null)
                                {
                                    t_book_list_arr[t_book.SiteId - 1].Add(t_book);
                                }
                                else
                                {
                                    List<SiteUrl> t_book_list = new List<SiteUrl>();
                                    t_book_list.Add(t_book);
                                    t_book_list_arr[t_book.SiteId - 1] = t_book_list;
                                }
                            }
                        }
                        catch (MySql.Data.MySqlClient.MySqlException e)
                        {
                            LogHelper.Error("获取更新数据失败" + e.ToString());
                        }
                    }
                }


                //那个站点小说最多
                int t_max_book_list_site = 0;

                foreach (List<SiteUrl> t_book_list in t_book_list_arr)
                {
                    if (t_book_list != null && t_book_list.Count > t_max_book_list_site)
                        t_max_book_list_site = t_book_list.Count;
                }

                //一次从一个站点中取一条数据进队列
                for (int index = 0; index < t_max_book_list_site; index++)
                {
                    foreach (List<SiteUrl> t_book_list in t_book_list_arr)
                    {
                        if (t_book_list != null && t_book_list.Count > index)
                        {
                            t_book_queue.Enqueue(t_book_list[index]);
                        }
                    }
                }
                return t_book_queue;

            }
            catch (Exception ex)
            {
                LogHelper.Error("获取更新队列失败" + ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 是否是已经完本的小说
        /// </summary>
        /// <param name="i_last_update_chatper_name"></param>
        /// <returns></returns>
        private bool IsComplateBook(string i_last_update_chatper_name)
        {
            if (string.IsNullOrEmpty(i_last_update_chatper_name))
                return false;

            if (i_last_update_chatper_name.IndexOf("新书") > -1)
                return true;

            if (i_last_update_chatper_name.IndexOf("完结") > -1)
                return true;

            if (i_last_update_chatper_name.IndexOf("结局") > -1)
                return true;

            return false;
        }

        /// <summary>
        /// 得到排序方式
        /// </summary>
        /// <param name="i_site_list"></param>
        /// <param name="i_site_id"></param>
        /// <returns></returns>
        private ChapterOrderType GetChapterOrderType(List<BookSiteInfo> i_site_list, int i_site_id)
        {
            ChapterOrderType t_order = ChapterOrderType.ASC;
            foreach (BookSiteInfo t_book_site in i_site_list)
            {
                if (t_book_site.SiteId == i_site_id)
                {
                    t_order = t_book_site.ChapterOrder;
                    break;
                }

            }
            return t_order;
        }

        /// <summary>
        /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>MySqlDataReader</returns>
        public MySqlDataReader ExecuteReader(string strSQL, string connectionString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(strSQL, connection))
                {
                    try
                    {
                        cmd.CommandTimeout = 600;
                        connection.Open();
                        MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        return myReader;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        throw e;
                    }
                }
            }

        }

         /// <summary>
        /// 获取小说章节列表
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public List<ChapterInfo> GetChapterByBookId(long i_book_id, string i_connect_string)
        {
            return null;
        }

        /// <summary>
        /// 更新小说下载状态
        /// </summary>
        /// <param name="i_chapter_id"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public int UpdateChapter(long i_book_id, long i_chapter_id, string i_chapter_url, BookUrlInfo i_book_url, string i_connect_string)
        {
            return 0;
        }

         /// <summary>
        /// 添加热点排行榜小说
        /// </summary>
        /// <param name="i_book_name"></param>
        /// <param name="i_book_source"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public long AddTopBook(string i_book_name, Consts.BookSource i_book_source, string i_connect_string)
        {
            return 0;
        }

          /// <summary>
        /// 添加图书点击
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <param name="i_connect_string"></param>
        public void AddBookClick(long i_book_id, string i_connect_string)
        {
        }


        public List<BookInfo> GetSEOBook(string i_connect_string)
        {
            return null;
        }

        public List<BookInfo> GetVIPBook(string i_connect_string)
        {
            return null;
        }

          /// <summary>
        /// 获取小说的章节地址列表
        /// </summary>
        /// <param name="i_book_id"></param>
        /// <param name="i_connect_string"></param>
        /// <returns></returns>
        public List<BookUrlInfo> GetBookUrlByBookId(long i_book_id, string i_connect_string)
        {
            return null;
        }

          /// <summary>
        /// 添加小说URL
        /// </summary>
        /// <param name="i_book_url"></param>
        /// <returns></returns>
        public int AddBookUrl(BookUrlInfo i_book_url, string i_connect_string)
        {
            return -1;
        }


        public List<BookUrlInfo> GetICONBookList(string i_connect_string)
        {
            return null;
        }

        public int UpdateBookUrl(long i_book_id, string i_icon_url, string i_connect_string)
        {
            return -1;
        }

        public List<BookInfo> GetSEOBookChapterContent(string i_connect_string)
        {
            return null;
        }
    }
}
